﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using prototype123.controllers;
using prototype123.models;
using System.Collections;

namespace prototype123.data_access
{
    //This class handles all data access between the application and the
    //course table in the database.
    class CourseDAO
    {
        private SqlConnection db_connection;

        public CourseDAO(SqlConnection connection)
        {
            db_connection = connection;
        }

        /*precondition: there is a need to get a single course object based on a courseid
        description: a course_id is passed into this method. It executes a sql command
              that selects a course from the db that matches this course_id. it then
           takes all info about that course and packages into a course object and returns is
        postcondition: a course object that matches course_id is returned to calling method*/
        public Course Load(string course_id)
        {

            Course course = new Course();
            SqlCommand getcourse = new SqlCommand("SELECT * FROM COURSE WHERE COURSE_ID = '" + course_id + "'", db_connection);
            SqlDataReader reader = getcourse.ExecuteReader();
            reader.Read();

            course.CourseID = (string)reader["COURSE_ID"];
            course.Credits = Convert.ToDouble(reader["CREDITS"]);
            course.ShortTitle = (string)reader["SHORT_TITLE"];
            course.SortSequence = Convert.ToInt32(reader["SORT_SEQ"]);
            course.Status = Convert.ToChar(reader["STATUS"]);
            course.Title = (string)reader["TITLE"];

            course.SubjectID = Convert.ToInt32(reader["SUBJECT_ID"]);
            course.TextbookID = Convert.ToInt32(reader["TEXTBOOK_ID"]);
            reader.Close();

            return course;
        }

        //Dersch is messing around with this alot. Caleb's code is commented out and obsolete and worthless
        //[insert witty retort here later when i think of a good one]

        /*preconditions: the controller calls this data access object and passes in valid needed information
        This method uses a student's ID (stuid) and the term the student is enrolled in (termid)
        and looks up all courses that stuid is enrolled in for the given termid.
        postcondition: An arraylist of course objects called "result" is returned to the controller*/
        public ArrayList Find(int stuid, int termid)
        {

            ArrayList classes = new ArrayList();
            ArrayList result = new ArrayList();

            SqlCommand getid = new SqlCommand("SELECT COURSE_ID FROM TERM_ITEM WHERE STU_ID = " + stuid +
                " and TERM_ID = " + termid, db_connection);


            SqlDataReader read = getid.ExecuteReader();

            while (read.Read())
            {
                classes.Add(read[0].ToString());
            }

            read.Close();

            foreach (string courseid in classes)
            {
                Course nextcourse = Load(courseid);
                result.Add(nextcourse);
            }

            return result;

        }

        /*precondition: the controller calls this in order to get a list of possible
              courses (so we have a list of options for a student to add)
        Description: This loads all possible courses in the course table. So basically, if
              the course exists, this grabs it. they are then all shoved into an arraylist
              named allcourses, and then that arraylist is returned.
        poscondition: An arraylist named allcourses containing course objects is returned
              to the controller*/
        public ArrayList LoadAllCourses()
        {
            ArrayList allcourses = new ArrayList();

            SqlCommand getcourse = new SqlCommand("SELECT * FROM COURSE", db_connection);
            SqlDataReader reader = getcourse.ExecuteReader();
            while (reader.Read())
            {
                Course course = new Course();
                course.CourseID = (string)reader["COURSE_ID"];
                course.Credits = Convert.ToDouble(reader["CREDITS"]);
                course.ShortTitle = (string)reader["SHORT_TITLE"];
                course.SortSequence = Convert.ToInt32(reader["SORT_SEQ"]);
                course.Status = Convert.ToChar(reader["STATUS"]);
                course.Title = (string)reader["TITLE"];
                course.SubjectID = Convert.ToInt32(reader["SUBJECT_ID"]);
                course.TextbookID = Convert.ToInt32(reader["TEXTBOOK_ID"]);
                allcourses.Add(course);
            }
            reader.Close();

            return allcourses;
        }




    }
}
